﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using 售后客户管理.Common;
using 售后客户管理.ModelLibrary;

namespace 售后客户管理.SqlClassLibrary
{
    public class SqlGetSet
    {
        static string SqlString = "";
        public static DataTable GetWorkPlanDT(WorkPlan workPlan, SqlConnection sqlConnection)
        {
            if (workPlan.planID != "")
            {
                SqlString = "SELECT yearMonthWeek,planType,taskType,ownerType,assistanceDepartment,assistanceStaff,principal,WORKLOAD,processRate,planStartDate,planCompleteDate,actualCompleteDate,taskDescription,planID,userName,planDate,beiZhu FROM RR_WorkPlan(NOLOCK) WHERE userName like @userName+'%' AND planID=@planID ORDER BY yearMonthWeek";
            }
            else
            {
                SqlString = "SELECT yearMonthWeek,planType,taskType,ownerType,assistanceDepartment,assistanceStaff,principal,WORKLOAD,processRate,planStartDate,planCompleteDate,actualCompleteDate,taskDescription,planID,userName,planDate,beiZhu FROM RR_WorkPlan(NOLOCK) WHERE userName like @userName+'%' ORDER BY yearMonthWeek";
            }
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@planID",workPlan.planID),
                new SqlParameter("@userName",workPlan.userName),
            };
            DataTable dataTable = SqlHelper.GetDataTable(SqlString, sqlConnection, param);
            if (dataTable.Rows.Count == 0)
                return null;
            return dataTable;
        }
        public static DataTable GetCustomerDT(Customer custin, SqlConnection sqlConnection)
        {
            if (custin.Custid == "")
                SqlString = "SELECT custid,beactive,Quyu,custbh,custname,pym,siteNum,dongleNum,custAdd,Shishi,lxr,lxdh,ModifyDate,beizhu,shangwu,Version FROM RR_Customers WHERE (pym LIKE '%'+@pym+'%' OR custbh like '%'+@custbh+'%' OR custName like '%'+@custname+'%')";
            else
                SqlString = "SELECT custid,beactive,Quyu,custbh,custname,pym,siteNum,dongleNum,custAdd,Shishi,lxr,lxdh,ModifyDate,beizhu,shangwu,Version FROM RR_Customers WHERE custid =@custid ";
            SqlParameter[] param = new SqlParameter[] {
                new SqlParameter("@custid",custin.Custid),
                new SqlParameter("@pym", custin.Pym),
                new SqlParameter("@custname", custin.Custname),
                new SqlParameter("@custbh",custin.Custbh) };

            DataTable dataTable = SqlHelper.GetDataTable(SqlString, sqlConnection, param);
            if (dataTable.Rows.Count == 0)
            {
                return null;
            }
            return dataTable;

        }
        public static DataTable GetHetongDT(Hetong htout, SqlConnection sqlConnection)
        {
            if (htout.Hetongid == "")
            {
                SqlString = "SELECT hetongid,hetongbh,beactive,hetongtype,shangwu,jiaofeiDate,custid,hetje,startdate,enddate,beizhu FROM RR_hetongs(NOLOCK) WHERE custid=@custid ORDER BY beactive DESC,enddate DESC";
            }
            else
            {
                SqlString = "SELECT hetongid,hetongbh,beactive,hetongtype,shangwu,jiaofeiDate,custid,hetje,startdate,enddate,beizhu FROM RR_hetongs(NOLOCK) WHERE custid=@custid AND hetongid =@hetongid ORDER BY beactive DESC,enddate DESC";
            }

            SqlParameter[] param = new SqlParameter[] {
                new SqlParameter("@custid", htout.Custid),
                new SqlParameter("@hetongid",htout.Hetongid) };

            DataTable dataTable = SqlHelper.GetDataTable(SqlString, sqlConnection, param);
            if (dataTable.Rows.Count == 0)
            {
                return null;
            }
            return dataTable;
        }
        public static DataTable GetSelectToDataTable(SqlConnection sqlConnection, string selectType, int days)
        {
            SqlParameter[] param = new SqlParameter[] {
                new SqlParameter("@selectType",selectType),
            new SqlParameter("@days",-1*days)};
            DataTable dt = SqlHelper.GetDataTable_Proc("GetSelect", sqlConnection, param);
            return dt;
        }
        public static List<string> GetDictList(string colname, SqlConnection sqlConnection)
        {

            SqlString = "SELECT dict FROM RR_column_dictionary WHERE beactive='Y' AND colname=@colname ORDER BY viewSort";

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",colname)
            };
            DataTable dt = SqlHelper.GetDataTable(SqlString, sqlConnection, param);

            List<string> dict = new List<string>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dict.Add(dt.Rows[i][0].ToString());
                }
            }
            return dict;
        }
        public static DataTable GetColumnDT(string StrGeting, SqlConnection sqlConnection)
        {
            SqlString = "SELECT DISTINCT colname,chnname,coltype,max_length,precise,beizhu FROM RR_ColumnsInfo WHERE colname LIKE '%'+@colname+'%' OR chnname LIKE '%'+@chnname+'%'";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",StrGeting),
                new SqlParameter("@chnname",StrGeting)
            };
            DataTable dt = SqlHelper.GetDataTable(SqlString, sqlConnection, param);

            return dt;
        }
        public static DataTable GetDictionaryDT(Dictionary dict, SqlConnection sqlConnection)
        {
            SqlString = "SELECT colname,chnname,beactive,dict,beizhu,viewSort FROM RR_column_dictionary WHERE colname = @colname AND chnname = @chnname ORDER BY viewSort";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",dict.Colname),
                new SqlParameter("@chnname",dict.Chnname)
            };
            DataTable dataTable = SqlHelper.GetDataTable(SqlString, sqlConnection, param);

            return dataTable;
        }
        public static string GetBh(string types, SqlConnection sqlConnection)
        {
            if (types == "hetong")
                SqlString = "DECLARE @hetongbh VARCHAR(10) " +
                            "SELECT @hetongbh = COUNT(1) + 1 FROM RR_hetongs " +
                            "SET @hetongbh = REPLICATE('0', 3 - LEN(@hetongbh)) + @hetongbh " +
                            "SELECT @hetongbh AS hetongbh ";
            else if (types == "customer")
            {
                SqlString = "DECLARE @custbh VARCHAR(10) " +
                            "SELECT @custbh = COUNT(1) + 1 FROM RR_Customers " +
                            "SET @custbh = REPLICATE('0', 5 - LEN(@custbh)) + @custbh " +
                            "SELECT @custbh AS custbh ";
            }
            return SqlHelper.SingleResult(SqlString, sqlConnection, null).ToString();
        }
        public static string GetColumnChnName(string ColumnEnName, SqlConnection sqlConnection)
        {
            SqlString = "SELECT chnname FROM RR_ColumnsInfo WHERE colname=@colname";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",ColumnEnName)
            };
            var chnNames = SqlHelper.SingleResult(SqlString, sqlConnection, param);
            string returns = "";
            if (chnNames == null)
            {
                returns = ColumnEnName;
            }
            else
            {
                returns = chnNames.ToString();
            }
            return returns;
        }
        public static DataTable GetSettingDataTable(Setting setting, SqlConnection sqlConnection)
        {
            if (setting.FId != "")
            {
                SqlString = "SELECT FId,SIds,SName,SLevel,DefaultValue,NowValue,shuoming FROM RR_Setting a WHERE FId=@Fid";
            }
            else
            {
                SqlString = "SELECT FId,SIds,SName,SLevel,DefaultValue,NowValue,shuoming FROM RR_Setting a WHERE a.SIds=@SIds OR SName =@SName";
            }
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@FId",setting.FId),
                 new SqlParameter("@SIds",setting.SIds),
                new SqlParameter("@SName",setting.SName),
            };
            DataTable dt = SqlHelper.GetDataTable(SqlString, sqlConnection, param);
            return dt;
        }
        public static int NewSetingId(string fid, SqlConnection sqlConnection)
        {
            SqlString = "SELECT ISNULL(MAX(SIds)+1,0) AS id FROM RR_Setting WHERE FId=@FId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@FId",fid),
            };
            var NewId = SqlHelper.SingleResult(SqlString, sqlConnection, param);
            return Convert.ToInt32(NewId);
        }



        public static int SetWorkPlan(WorkPlan workPlan, SqlConnection sqlConnection)
        {
            if (workPlan.planID == "")
            {
                SqlString = "SET DATEFIRST 1   INSERT INTO RR_WorkPlan(userName,planType,taskType,ownerType,assistanceDepartment,assistanceStaff,principal,WORKLOAD,processRate,yearMonthWeek,planStartDate,planCompleteDate,actualCompleteDate,taskDescription,planDate,beiZhu)" +
                    "VALUES(@userName, @planType, @taskType, @ownerType, @assistanceDepartment, @assistanceStaff, @principal, @WORKLOAD, @processRate, dbo.get_yearMonthWeek(@yearMonthWeek), @planStartDate, @planCompleteDate, @actualCompleteDate, @taskDescription,@planDate, @beiZhu)";
            }
            else
            {
                SqlString = "SET DATEFIRST 1   UPDATE a SET " +
                        "a.userName = @userName," +
                        "a.planType = @planType," +
                        "a.taskType = @taskType," +
                        "a.ownerType = @ownerType," +
                        "a.assistanceDepartment = @assistanceDepartment," +
                        "a.assistanceStaff = @assistanceStaff," +
                        "a.principal = @principal," +
                        "a.WORKLOAD = @WORKLOAD," +
                        "a.processRate = @processRate," +
                        "a.yearMonthWeek = dbo.get_yearMonthWeek(@yearMonthWeek)," +
                        "a.planStartDate = @planStartDate," +
                        "a.planCompleteDate = @planCompleteDate," +
                        "a.actualCompleteDate = @actualCompleteDate," +
                        "a.planDate = @planDate," +
                        "a.taskDescription = @taskDescription," +
                        "a.beiZhu = @beiZhu " +
                        "FROM RR_WorkPlan a " +
                        "WHERE a.planID = @planID";
            }

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@planid",workPlan.planID),
                new SqlParameter("@userName",workPlan.userName),
                new SqlParameter("@planType",workPlan.planType),
                new SqlParameter("@taskType",workPlan.taskType),
                new SqlParameter("@ownerType",workPlan.ownerType),
                new SqlParameter("@assistanceDepartment",workPlan.assistanceDepartment),
                new SqlParameter("@assistanceStaff",workPlan.assistanceStaff),
                new SqlParameter("@principal",workPlan.principal),
                new SqlParameter("@WORKLOAD",workPlan.workLoad),
                new SqlParameter("@processRate",workPlan.processRate),
                new SqlParameter("@yearMonthWeek",workPlan.planDate),
                new SqlParameter("@planDate",workPlan.planDate),
                new SqlParameter("@planStartDate",workPlan.planStartDate),
                new SqlParameter("@planCompleteDate",workPlan.planCompleteDate),
                new SqlParameter("@actualCompleteDate",workPlan.actualCompleteDate),
                new SqlParameter("@taskDescription",workPlan.taskDescription),
                new SqlParameter("@beiZhu",workPlan.beiZhu),
            };

            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int SetHetong(Hetong htin, SqlConnection sqlConnection)
        {
            if (htin.Hetongid == "")
            {
                SqlString = "INSERT INTO rr_hetongs(custid,beactive,hetongbh,hetongtype,shangwu,jiaofeiDate,hetje,startdate,enddate,beizhu) SELECT @custid,@beactive,@hetongbh,@hetongtype,@shangwu,CONVERT(VARCHAR(10),CONVERT(DATETIME,@jiaofeiDate),121),@hetje,CONVERT(VARCHAR(10),CONVERT(DATETIME,@startdate),121),CONVERT(VARCHAR(10),CONVERT(DATETIME,@enddate),121),@beizhu";
            }
            else
            {
                SqlString = "UPDATE a SET a.hetongbh=@hetongbh,a.beactive=@beactive,a.hetongtype=@hetongtype,a.shangwu=@shangwu,a.jiaofeiDate=CONVERT(VARCHAR(10),CONVERT(DATETIME,@jiaofeiDate),121),a.hetje=@hetje,a.startdate=CONVERT(VARCHAR(10),CONVERT(DATETIME,@startdate),121),a.enddate=CONVERT(VARCHAR(10),CONVERT(DATETIME,@enddate),121),a.beizhu=@beizhu FROM RR_hetongs a WHERE hetongid=@hetongid AND custid=@custid";
            }

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@custid",htin.Custid),
                new SqlParameter("@beactive",htin.Beactive),
                new SqlParameter("@hetongid",htin.Hetongid),
                new SqlParameter("@hetongbh",htin.Hetongbh),
                new SqlParameter("@hetongtype",htin.Hetongtype),
                new SqlParameter("@hetje",htin.Hetje),
                new SqlParameter("@startdate",htin.Startdate),
                new SqlParameter("@shangwu",htin.Shangwu),
                new SqlParameter("@jiaofeiDate",htin.JiaofeiDate),
                new SqlParameter("@enddate",htin.Enddate),
                new SqlParameter("@beizhu",htin.Beizhu)
            };

            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int SetCustomer(Customer custin, SqlConnection sqlConnection)
        {
            SqlString =
                " IF NOT EXISTS(SELECT 1 FROM RR_Customers(NOLOCK) WHERE custid=@custid)" +
                " BEGIN " +
                " INSERT INTO RR_Customers(beactive, custbh, custname, pym, custAdd, lxr, lxdh, beizhu, siteNum,dongleNum,shangwu,Version,Shishi,Quyu)" +
                " SELECT @beactive, @custbh, @custname, dbo.getPY(@custname), @custAdd, @lxr, @lxdh, @beizhu, @sitenum,@donglenum,@shangwu,@Version,@Shishi,@Quyu" +
                " END" +
                " ELSE" +
                " BEGIN" +
                " UPDATE a SET a.custbh = @custbh, a.beactive = @beactive, a.custname = @custname, a.pym = dbo.getPY(@custname), a.custadd = @custAdd, a.lxr = @lxr, a.lxdh = @lxdh, a.beizhu = @beizhu, a.siteNum=@sitenum, a.ModifyDate=CONVERT([varchar](23),getdate(),(121)),a.dongleNum=@donglenum,a.shangwu=@shangwu,a.Version=@Version,a.Shishi=@Shishi,a.Quyu=@Quyu" +
                " FROM RR_Customers a WHERE custid = @custid" +
                " END";

            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@custid",custin.Custid),
                new SqlParameter("@custbh",custin.Custbh),
                new SqlParameter("@custname",custin.Custname),
                new SqlParameter("@custAdd",custin.CustAdd),
                new SqlParameter("@beizhu",custin.Beizhu),
                new SqlParameter("@sitenum",custin.Sitenum),
                new SqlParameter("@donglenum",custin.Donglenum),
                new SqlParameter("@lxr",custin.Lxr),
                new SqlParameter("@lxdh",custin.Lxdh),
                new SqlParameter("@Shishi",custin.Shishi),
                new SqlParameter("@Quyu",custin.Quyu),
                new SqlParameter("@shangwu",custin.Shangwu),
                new SqlParameter("@beactive",custin.Beactive),
                new SqlParameter("@Version",custin.Version),
            };
            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int SetDictionary(Dictionary dict, SqlConnection sqlConnection)
        {
            SqlString = " IF NOT EXISTS(SELECT 1 FROM RR_column_dictionary WHERE colname=@colname AND (dict=@dict_old OR dict=@dict))" +
                " INSERT INTO RR_column_dictionary(colname,chnname,beactive,dict,beizhu,viewSort)SELECT @colname,@chnname,@beactive,@dict,@beizhu,@viewSort" +
                " ELSE" +
                " UPDATE a SET colname=@colname,chnname=@chnname,beactive=@beactive,dict=@dict,beizhu=@beizhu,viewSort=@viewSort FROM RR_column_dictionary a" +
                " WHERE colname=@colname AND (dict=@dict_old OR dict=@dict)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",dict.Colname),
                new SqlParameter("@chnname",dict.Chnname),
                new SqlParameter("@beactive",dict.Beactive),
                new SqlParameter("@dict",dict.Dict),
                new SqlParameter("@beizhu",dict.Beizhu),
                new SqlParameter("@dict_old",dict.OldDict),
                new SqlParameter("@viewSort",dict.ViewSort),
            };
            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int SetColumn(Column column, SqlConnection sqlConnection)
        {
            SqlString = "IF NOT EXISTS(SELECT 1 FROM RR_ColumnsInfo(NOLOCK) WHERE colname=@colname OR colname =@oldcolname)" +
                " INSERT INTO RR_ColumnsInfo(colname,chnname,coltype,max_length,precise,beizhu)" +
                " SELECT @colname,@chnname,@coltype,@max_length,@precise,@beizhu" +
                " ELSE" +
                " UPDATE a SET a.chnname=@chnname,a.coltype=@coltype,a.max_length=@max_length,a.precise=@precise,a.beizhu=@beizhu FROM RR_ColumnsInfo a " +
                " WHERE a.chnname=@chnname AND (a.colname=@colname OR a.colname=@oldcolname)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@colname",column.Colname),
                new SqlParameter("@chnname",column.Chnname),
                new SqlParameter("@coltype",column.Coltype),
                new SqlParameter("@max_length",column.Max_length),
                new SqlParameter("@precise",column.Precise),
                new SqlParameter("@beizhu",column.Beizhu),
                new SqlParameter("@oldcolname",column.Oldcolname),
            };
            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int SetSetting(Setting setting, SqlConnection sqlConnection)
        {
            SqlString = " IF NOT EXISTS(SELECT 1 FROM RR_Setting WHERE SIds=@SIds)" +
                " INSERT INTO RR_Setting(FId,SIds,SName,SLevel,DefaultValue,NowValue,shuoming) SELECT @FId, @SIds, @SName, @SLevel, @DefaultValue, @NowValue, @shuoming" +
                " ELSE" +
                " UPDATE a SET a.FId=@FId,a.SIds=@SIds,a.SName=@SName,a.SLevel=@SLevel,a.DefaultValue=@DefaultValue,a.NowValue=@NowValue,a.shuoming=@shuoming FROM RR_Setting a" +
                " WHERE SIds=@Sids";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@FId",setting.FId),
                new SqlParameter("@Sids",setting.SIds),
                new SqlParameter("@SName",setting.SName),
                new SqlParameter("@SLevel",setting.SLevel),
                new SqlParameter("@DefaultValue",setting.DefaultValue),
                new SqlParameter("@NowValue",setting.NowValue),
                new SqlParameter("@shuoming",setting.shuoming),
            };
            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
        public static int DeleteSetting(Setting setting, SqlConnection sqlConnection)
        {
            SqlString = " DELETE RR_Setting WHERE SIds=@SIds";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@Sids",setting.SIds),
            };
            int rowcount = SqlHelper.InsertUpdateDelete(SqlString, sqlConnection, param);
            return rowcount;
        }
    }
}
